﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.ServiceModel.DomainServices.EntityFramework;
using System.ServiceModel.DomainServices.Server;
using System.Transactions;
using System.Web;
using System.Web.Security;
using BarcodeLib.Symbologies;
using GSSoftSL.Web.Models;
using GSSoftSL.Web.Models.Shared;
using Newtonsoft.Json;

namespace GSSoftSL.Web.Services
{
    public partial class GSSoftService
    {
        #region Phân quyền Main view

        [Invoke]
        public string[] GetDanhSachQuyen()
        {
            return Roles.GetRolesForUser();
        }

        #endregion

        #region Trường

        public void InsertTruong(Truong truong)
        {
            truong.MaTruong = (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaTruong AS INT)), 0) FROM Truong").
                                   Single() + 1).ToString().PadLeft(4, '0');
            if ((truong.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(truong, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Truongs.AddObject(truong);
            }
        }

        #endregion

        public IQueryable<GiaoVien> GetGiaoViensWithDetails()
        {
            return this.ObjectContext.GiaoViens.Include("GiaoVienMons");
        }

        public IQueryable<Mon> GetMonsWithGiaoVienMons()
        {
            //return this.ObjectContext.ExecuteStoreQuery<Mon>(@"SELECT m.* FROM Mon m INNER JOIN GiaoVienMon gvm ON m.MaMon = gvm.MaMon");
            //return this.ObjectContext.Mons.Include("GiaoVienMons").Include("GiaoVienMons.GiaoVien").Where(n => n.GiaoVienMons.Count > 0);
            return this.ObjectContext.Mons.Include("GiaoVienMons").Where(n => n.GiaoVienMons.Count > 0);
                //.Include("GiaoVienMons.GiaoVien");
        }

        public IQueryable<Lop> GetLopsWithGiaoViens_Mons()
        {
            return
                this.ObjectContext.Lops.Include("GiaoVien").Include("Mon").Include("LopChiTiets").Include(
                    "LopChiTiets.Phong");
        }

        public IQueryable<Lop> GetLopsWithGiaoVien()
        {
            return this.ObjectContext.Lops.Include("GiaoVien");
        }

        /*public int GetMaxMaGV()
        {
            return
                this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaGV AS INT)), 0) FROM GiaoVien").
                    Single();
        }*/

        

        public void UpdateGiaoVien(GiaoVien currentGiaoVien)
        {
            //this.ObjectContext.GiaoViens.AttachAsModified(currentGiaoVien, this.ChangeSet.GetOriginal(currentGiaoVien));

            // Add the entire graph so duplicate keys on new Entities are allowed.
            this.ObjectContext.GiaoViens.AddObject(currentGiaoVien);

            // Get the root Entity into the correct state.
            GiaoVien original = this.ChangeSet.GetOriginal(currentGiaoVien);

            if (original == null)
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(currentGiaoVien,
                                                                        EntityState.Unchanged);
            }
            else
            {
                this.ObjectContext.GiaoViens.AttachAsModified(currentGiaoVien, original);
            }

            // Loop through all children and get them into the correct state.
            foreach (GiaoVienMon c in this.ChangeSet.GetAssociatedChanges(currentGiaoVien, p => p.GiaoVienMons))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.GiaoVienMons.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.GiaoVienMons.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }
        }

        public void InsertGiaoVien(GiaoVien giaoVien)
        {
            giaoVien.MaGV = (this.ObjectContext.ExecuteStoreQuery<int>(
                "SELECT ISNULL(MAX(CAST(MaGV AS INT)), 0) FROM GiaoVien").Single() + 1).ToString().PadLeft(4, '0');
            if ((giaoVien.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(giaoVien, EntityState.Added);
            }
            else
            {
                this.ObjectContext.GiaoViens.AddObject(giaoVien);
            }
        }


        #region Mon

        public void InsertMon(Mon mon)
        {
            mon.MaMon =
                (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaMon AS INT)), 0) FROM Mon").
                     Single() + 1).ToString().PadLeft(3, '0');
            if ((mon.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(mon, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Mons.AddObject(mon);
            }
        }

        #endregion

        #region Lop

        public void UpdateLop(Lop currentLop)
        {
            // Add the entire graph so duplicate keys on new Entities are allowed.
            this.ObjectContext.Lops.AddObject(currentLop);

            // Get the root Entity into the correct state.
            Lop original = this.ChangeSet.GetOriginal(currentLop);

            if (original == null)
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(currentLop, EntityState.Unchanged);
            }
            else
            {
                this.ObjectContext.Lops.AttachAsModified(currentLop, original);
            }

            // Loop through all children and get them into the correct state.
            foreach (LopChiTiet c in this.ChangeSet.GetAssociatedChanges(currentLop, p => p.LopChiTiets))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.LopChiTiets.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.LopChiTiets.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }

            foreach (KhoaHoc c in this.ChangeSet.GetAssociatedChanges(currentLop, p => p.KhoaHocs))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.KhoaHocs.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.KhoaHocs.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }
        }

        public void InsertLop(Lop lop)
        {
            lop.MaLop =
                (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaLop AS INT)), 0) FROM Lop").Single() +
                 1).ToString().PadLeft(4, '0');
            if ((lop.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(lop, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Lops.AddObject(lop);
            }
        }

        /*public int GetMaxMaLop()
        {
            return
                this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaLop AS INT)), 0) FROM Lop").Single();
        }*/

        public IQueryable<Lop> GetLopsWithKhoaHocs()
        {
            return this.ObjectContext.Lops.Include("KhoaHocs").Include("GiaoVien");
        }

        #endregion

        #region KhoaHoc

        public void InsertKhoaHoc(KhoaHoc khoaHoc)
        {
            khoaHoc.MaKhoaHoc = khoaHoc.Lop.MaLop + 
                (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(STT), 0) FROM KhoaHoc WHERE LopId = {0}", khoaHoc.LopId).
                     Single() + 1).ToString().PadLeft(4, '0');
            if ((khoaHoc.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(khoaHoc, EntityState.Added);
            }
            else
            {
                this.ObjectContext.KhoaHocs.AddObject(khoaHoc);
            }
        }

        public IQueryable<KhoaHoc> GetKhoaHocsWithLop()
        {
            return this.ObjectContext.KhoaHocs.Include("Lop");
        }

        #endregion

        #region HocVien

        public void InsertHocVien(HocVien hocVien)
        {
            hocVien.STT = (this.ObjectContext.ExecuteStoreQuery<long>("SELECT ISNULL(MAX(STT), 0) FROM HocVien").Single() + 1);
            EAN13 ean = new EAN13("100" + hocVien.STT.ToString().PadLeft(9, '0'));
            hocVien.MaHV = ean.RawData;
            if ((hocVien.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(hocVien, EntityState.Added);
            }
            else
            {
                this.ObjectContext.HocViens.AddObject(hocVien);
            }
        }

        public IQueryable<HocVien> GetHocVienFilters(string MaHV, string HoLot, string Ten, int? GioiTinh, string Email, string DienThoai)
        {
            var query = this.ObjectContext.HocViens as IQueryable<HocVien>;//.Where(n => n.MaHV.Contains("123")).Where(n => n.Ten.Contains("123"));
            if (MaHV != "")
                query = query.Where(n => n.MaHV.Contains(MaHV));
            if (HoLot != "")
                query = query.Where(n => n.HoLot.Contains(HoLot));
            if (Ten != "")
                query = query.Where(n => n.Ten.Contains(Ten));
            if (Email != "")
                query = query.Where(n => n.Email.Contains(Email));
            if (DienThoai != "")
                query = query.Where(n => n.DTCaNhan.Contains(DienThoai));
            if (GioiTinh.HasValue)
                query = query.Where(n => n.GioiTinh == GioiTinh);
            return query;
        }

        public IQueryable<Lop> GetLopsHocVienTheoHoc(string MaHV)
        {
            var query =
                this.ObjectContext.LopHocViens.Include("Lop").Include("Lop.GiaoVien").Where(
                    n => n.MaHV == MaHV && n.ConTheoHoc).Select(n => n.Lop);
            return query;
        }

        /*public EntityCollection<Lop> GetLopsHocVienTheoHoc(string MaHV)
        {
            var query = this.ObjectContext.HocViens.Include("Lops").Include("Lops.GiaoVien").Single(n => n.MaHV == MaHV).Lops;
            return query;
            //return this.ObjectContext.HocViens.Single(n => n.MaHV == MaHV).Lops;
            /*var query = from HocVien hv in this.ObjectContext.HocViens.Include("Lops.GiaoVien").Where(n => n.MaHV == MaHV)
                        select hv.Lops;#1#
            /*var test = this.ObjectContext.Lops.Include("GiaoVien").ToArray();#1#
            /*var query = from Lop lop in this.ObjectContext.Lops.Include("GiaoVien")
                        join Lop l in this.ObjectContext.HocViens.First(n => n.MaHV == MaHV).Lops
                            on lop.LopId equals l.LopId
                        select lop;#1#
            /*HocVien[] test2 = this.ObjectContext.HocViens.Include("Lops.GiaoVien").ToArray();
            HocVien[] test3 = this.ObjectContext.HocViens.ToArray();
            foreach (HocVien hv in test2)
            {
                
            }#1#
            /*string sql =
                @"SELECT 
[Extent1].[TrangThai] AS [TrangThai], 
[Extent1].[LopId] AS [LopId], 
[Extent1].[GiaoVienId] AS [GiaoVienId], 
[Extent1].[MaMon] AS [MaMon], 
[Extent1].[MaLop] AS [MaLop], 
[Extent1].[TenLop] AS [TenLop], 
[Extent1].[NguoiTao] AS [NguoiTao], 
[Extent1].[NgayTao] AS [NgayTao], 
[Extent1].[NguoiCapNhat] AS [NguoiCapNhat], 
[Extent1].[NgayCapNhat] AS [NgayCapNhat], 
[Extent1].[HocPhiDeNghi] AS [HocPhiDeNghi], 
[Extent2].[GiaoVienID] AS [GiaoVienId1], 
[Extent2].[MaGV] AS [MaGV], 
[Extent2].[TenGV] AS [TenGV], 
[Extent2].[DiaChi] AS [DiaChi], 
[Extent2].[DienThoai] AS [DienThoai], 
[Extent2].[GioiTinh] AS [GioiTinh], 
[Extent2].[Email] AS [Email], 
[Extent2].[GhiChu] AS [GhiChu], 
[Extent2].[NgaySinh] AS [NgaySinh], 
[Extent2].[NguoiTao] AS [NguoiTao1], 
[Extent2].[NgayTao] AS [NgayTao1], 
[Extent2].[NguoiCapNhat] AS [NguoiCapNhat1], 
[Extent2].[NgayCapNhat] AS [NgayCapNhat1]
FROM  [dbo].[Lop] AS [Extent1]
INNER JOIN [dbo].[GiaoVien] AS [Extent2] ON [Extent1].[GiaoVienId] = [Extent2].[GiaoVienID]
INNER JOIN LopHocVien [lhv] ON [Extent1].[LopId] = [lhv].[LopId]
WHERE [lhv].[MaHV] = {0}";
            var query = this.ObjectContext.ExecuteStoreQuery<Lop>(sql, MaHV);#1#
            //return query;// (query as IQueryable<Lop>);
        }*/

        public void RegisterLopsHocVien(string MaHV, Guid[] Lops)
        {
            var lst = this.ObjectContext.LopHocViens.Where(n => n.MaHV == MaHV).ToArray();
            var lstUpdateIdKhongTheo = lst.Select(n => n.LopId).Except(Lops);
            var lstUpdateKhongTheo = from LopHocVien lhv in lst
                                     join Guid g in lstUpdateIdKhongTheo on lhv.LopId equals g
                                     select lhv;
            foreach (LopHocVien lhv in lstUpdateKhongTheo)
                lhv.ConTheoHoc = false;

            var lstUpdateIdTheo = lst.Select(n => n.LopId).Intersect(Lops);
            var lstUpdateTheo = from LopHocVien lhv in lst
                                join Guid g in lstUpdateIdTheo on lhv.LopId equals g
                                select lhv;
            foreach (LopHocVien lhv in lstUpdateTheo)
                lhv.ConTheoHoc = true;

            var lstAddId = Lops.Except(lst.Select(n => n.LopId));
            foreach (Guid lop in lstAddId)
            {
                this.ObjectContext.LopHocViens.AddObject(new LopHocVien()
                                                             {
                                                                 MaHV = MaHV,
                                                                 LopId = lop,
                                                                 ConTheoHoc = true
                                                             });
            }
            this.ObjectContext.SaveChanges();
            /*using (TransactionScope tran = new TransactionScope())
            {
                Guid[] lstLops = this.ObjectContext.ExecuteStoreQuery<Guid>("SELECT LopId FROM LopHocVien WHERE MaHV = {0}", MaHV).ToArray();
                this.ObjectContext.ExecuteStoreCommand("UPDATE LopHocVien SET ConTheoHoc = 0 WHERE MaHV = {0}", MaHV);
                Guid[] lstUpdate = Lops.Intersect(lstLops).ToArray();
                Guid[] lstInsert = Lops.Except(lstLops).ToArray();
            }*/
            /*string sql = "INSERT INTO LopHocVien VALUES({0}, {1})";
            using (TransactionScope tran = new TransactionScope())
            {
                this.ObjectContext.ExecuteStoreCommand("DELETE FROM LopHocVien WHERE MaHV = {0}", MaHV);
                foreach (var lop in Lops)
                    this.ObjectContext.ExecuteStoreCommand(sql, lop, MaHV);
                   
                tran.Complete();
            }*/
            //this.ObjectContext.CreateObjectSet<Lop>()
        }

        public LopHocPhi[] GetLopsDaDongHocPhi(string MaHV)
        {
            string sql =
                @"SELECT	kh.KhoaHocId, kh.LopId, kh.MaKhoaHoc, kh.TenKhoaHoc, kh.BatDau, kh.KetThuc, 
		                        l.TenLop, m.TenMon, gv.TenGV, l.HocPhiDeNghi, CAST(0 AS numeric(18,2)) AS MucGiam, l.HocPhiDeNghi AS HocPhiSauGiam,
		                        CAST(CASE 
				                        WHEN khct.KhoaHocId IS NULL THEN 0
				                        ELSE 1
			                            END AS BIT) DaDong
                        FROM
                        (SELECT LopId FROM LopHocVien WHERE MaHV = {0}) AS lop
                        INNER JOIN KhoaHoc kh ON lop.LopId = kh.LopId
                        INNER JOIN Lop l ON l.LopId = lop.LopId
                        INNER JOIN Mon m ON m.MaMon = l.MaMon
                        INNER JOIN GiaoVien gv ON gv.GiaoVienID = l.GiaoVienId
                        LEFT JOIN
                        (
	                        SELECT DISTINCT blc.KhoaHocId 
                            FROM BienLai bl INNER JOIN BienLaiCT blc ON bl.BienLaiId = blc.BienLaiId
                            WHERE bl.MaHV = {0}
                        ) AS khct ON kh.KhoaHocId = khct.KhoaHocId
                        ORDER BY m.MaMon";
            LopHocPhi[] res = this.ObjectContext.ExecuteStoreQuery<LopHocPhi>(sql, MaHV).ToArray();
            return res;
            //return null;
        }

        /*public void DongHocPhi(string MaHV, )
        {
            
        }*/

        #endregion

        #region Phong

        public void InsertPhong(Phong phong)
        {
            phong.MaPhong = (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaPhong AS INT)), 0) FROM Phong").
                                   Single() + 1).ToString().PadLeft(4, '0');
            if ((phong.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(phong, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Phongs.AddObject(phong);
            }
        }

        #endregion

        #region BienLai

        public void InsertBienLai(BienLai bienLai)
        {
            int max =
                this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(SUBSTRING(SoBienLai, 3, 6) AS INT)), 0) FROM BienLai").
                    Single();
            string soBienLai = DateTime.Now.Year.ToString().Substring(2, 2) + (max + 1).ToString().PadLeft(6, '0');
            bienLai.SoBienLai = soBienLai;
            bienLai.NgayTao = DateTime.Now;
            if ((bienLai.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(bienLai, EntityState.Added);
            }
            else
            {
                this.ObjectContext.BienLais.AddObject(bienLai);
            }
        }

        public void UpdateBienLai(BienLai currentBienLai)
        {
            // Add the entire graph so duplicate keys on new Entities are allowed.
            this.ObjectContext.BienLais.AddObject(currentBienLai);

            // Get the root Entity into the correct state.
            BienLai original = this.ChangeSet.GetOriginal(currentBienLai);

            if (original == null)
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(currentBienLai, EntityState.Unchanged);
            }
            else
            {
                this.ObjectContext.BienLais.AttachAsModified(currentBienLai, original);
            }

            // Loop through all children and get them into the correct state.
            foreach (BienLaiCT c in this.ChangeSet.GetAssociatedChanges(currentBienLai, p => p.BienLaiCTs))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.BienLaiCTs.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.BienLaiCTs.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }
        }

        #endregion

        #region Điểm danh

        /*
        SELECT	hv.MaHV, hv.NgaySinh, hv.HoLot + ' ' + hv.Ten HoTen, ISNULL(ddc.CoPhep, 0) CoPhep, ddc.LyDo,
		CASE ddc.MaHV
			WHEN NULL THEN 0
			ELSE 1
		END Vang
FROM HocVien hv 
LEFT JOIN (SELECT * FROM LopHocVien WHERE LopId = '')  lhv ON hv.MaHV = lhv.MaHV
LEFT JOIN (SELECT * FROM DiemDanhCT WHERE DiemDanhId = '') ddc ON hv.MaHV = ddc.MaHV
        */

        public DiemDanhCTInfo[] GetDiemDanhCTInfo(Guid LopId, Guid DiemDanhId)
        {
            return
                this.ObjectContext.ExecuteStoreQuery<DiemDanhCTInfo>(
                    @"
SELECT hv.MaHV, hv.NgaySinh, hv.HoLot + ' ' + hv.Ten HoTen, t.Vang, t.CoPhep, t.LyDo
FROM HocVien hv INNER JOIN
(
	SELECT	ISNULL(t1.MaHV, t2.MaHV) MaHV, 
			CAST(CASE 
				    WHEN t2.MaHV IS NULL THEN 0
				    ELSE 1  
			     END AS BIT) Vang,
			CAST(ISNULL(t2.CoPhep, 0) AS BIT) CoPhep, t2.LyDo
	FROM
	(SELECT MaHV FROM LopHocVien WHERE LopId = {0} AND ConTheoHoc = 1) t1
	FULL OUTER JOIN
	(SELECT MaHV, CoPhep, LyDo FROM DiemDanhCT WHERE DiemDanhId = {1}) t2
	ON t1.MaHV = t2.MaHV
) t
ON hv.MaHV = t.MaHV", LopId, DiemDanhId)
                    .ToArray();
        }

        public DiemDanhCTInfo[] GetDiemDanhDSHV(Guid LopId)
        {
            var query = 
                this.ObjectContext.ExecuteStoreQuery<DiemDanhCTInfo>(
                    @"
SELECT	hv.MaHV, hv.NgaySinh, hv.HoLot + ' ' + hv.Ten HoTen, CAST(0 AS BIT) Vang, CAST(0 AS BIT) CoPhep, N'' LyDo
FROM HocVien hv 
INNER JOIN (SELECT * FROM LopHocVien WHERE LopId = {0}) lhv 
ON hv.MaHV = lhv.MaHV", LopId)
                    .ToArray();
            return query;
        }

        [Invoke]
        public void DiemDanhAddNew(DiemDanh master, List<DiemDanhCT> details)
        {
            this.ObjectContext.DiemDanhs.AddObject(master);
            foreach (DiemDanhCT diemDanhCt in details)
            {
                this.ObjectContext.DiemDanhCTs.AddObject(diemDanhCt);
            }
            this.ObjectContext.SaveChanges();
            //this.ObjectContext.DiemDanhCTs.AddObject();
        }

        [Invoke]
        public void DiemDanhUpdate(DiemDanh master, List<DiemDanhCT> details)
        {
            // Add the entire graph so duplicate keys on new Entities are allowed.
            //this.ObjectContext.DiemDanhs.AddObject(master);
            /*this.ObjectContext.DiemDanhs.AttachAsModified(master,
                                                          this.ObjectContext.DiemDanhs.Single(
                                                              n => n.DiemDanhId == master.DiemDanhId));*/
            var original = this.ObjectContext.DiemDanhs.Single(n => n.DiemDanhId == master.DiemDanhId);
            original.NguoiCapNhat = master.NguoiCapNhat;
            original.NgayCapNhat = master.NgayCapNhat;
            original.SoLuongHV = master.SoLuongHV;
            original.SoLuongVang = master.SoLuongVang;
            original.CoGiaoVien = master.CoGiaoVien;
            //this.ObjectContext.ObjectStateManager.ChangeObjectState(master, EntityState.Modified);
            // Get the root Entity into the correct state.
            //DiemDanh original = this.ChangeSet.GetOriginal(master);
            /*if (original == null)
                this.ObjectContext.ObjectStateManager.ChangeObjectState(master, EntityState.Unchanged);
            else
                this.ObjectContext.DiemDanhs.AttachAsModified(master, original);*/

            var delete = this.ObjectContext.DiemDanhCTs.Where(n => n.DiemDanhId == master.DiemDanhId);
            foreach (DiemDanhCT ct in delete)
                this.ObjectContext.DiemDanhCTs.DeleteObject(ct);

            foreach (DiemDanhCT ct in details)
                this.ObjectContext.DiemDanhCTs.AddObject(ct);
            this.ObjectContext.SaveChanges();
        }

        #endregion

        #region Điểm

        public void InsertLopCotDiem(LopCotDiem lopCotDiem)
        {
            /*khoaHoc.MaKhoaHoc = khoaHoc.Lop.MaLop +
                (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(STT), 0) FROM KhoaHoc WHERE LopId = {0}", khoaHoc.LopId).
                     Single() + 1).ToString().PadLeft(4, '0');*/
            lopCotDiem.STT =
                this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(STT), 0) FROM LopCotDiem WHERE LopId = {0}",
                                                          lopCotDiem.LopId).Single() + 1;

            if ((lopCotDiem.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(lopCotDiem, EntityState.Added);
            }
            else
            {
                this.ObjectContext.LopCotDiems.AddObject(lopCotDiem);
            }
        }

        [Invoke]
        public CotDiemInfo[] GetCotDiemInfos(Guid LopId)
        {
            return this.ObjectContext.LopCotDiems.Where(n => n.LopId == LopId)
                .Select(n => new CotDiemInfo()
                                 {
                                     STT = n.STT,
                                     TenCot = n.Ten,
                                     IsSelected = false,
                                     KhoaHocId = n.KhoaHocId
                                 }).ToArray();
        }

        /*
SELECT hv.MaHV, hv.HoLot + ' ' + hv.Ten HoTen, Cot1, Cot2
FROM HocVien hv
INNER JOIN
(
	SELECT MaHV, [1] Cot1, [2] Cot2
	FROM 
	(
		SELECT MaHV, [Cot], Diem FROM LopDiem
		WHERE LopId = '0F157A45-A862-470A-9451-8CF25C06F87B'
	) p
	PIVOT
	(
		SUM(Diem)
		FOR [Cot] IN ([1], [2])
	) AS pvt
) d ON hv.MaHV = d.MaHV
ORDER BY hv.MaHV
 */
        [Invoke]
        public string GetJsonDiemsByLop(Guid LopId)
        {
            DiemSo diem = new DiemSo();
            diem.LopId = LopId;
            var CotDiems = this.ObjectContext.LopCotDiems.Where(n => n.LopId == LopId)
                .Select(n => new CotDiemInfo()
                                 {
                                     STT = n.STT,
                                     TenCot = n.Ten
                                 }).ToList();
            string strIn = string.Join(",", CotDiems.Select(n => "[" + n.STT + "]"));
            string strSelect1 = string.Join(",", CotDiems.Select(n => "Cot" + n.STT));
            string strSelect2 = string.Join(",", CotDiems.Select(n => "[" + n.STT + "] Cot" + n.STT ));
            string sql = string.Format(@"
SELECT hv.MaHV, hv.HoLot + ' ' + hv.Ten HoTen, {0}
FROM HocVien hv
INNER JOIN
(
	SELECT MaHV, {1}
	FROM 
	(
		SELECT MaHV, [Cot], Diem FROM LopDiem
		WHERE LopId = {{0}}
	) p
	PIVOT
	(
		SUM(Diem)
		FOR [Cot] IN ({2})
	) AS pvt
) d ON hv.MaHV = d.MaHV
ORDER BY hv.MaHV
", strSelect1, strIn, strSelect2);
            diem.Diems = this.ObjectContext.ExecuteStoreQuery<HocVienDiem>(sql, LopId).ToList();
            return JsonConvert.SerializeObject(diem, Formatting.Indented,
                                               new JsonSerializerSettings {NullValueHandling = NullValueHandling.Ignore});

            //return null;
        }

        [Invoke]
        public string GetJsonDiemsByLopCots(Guid LopId, int[] Cots)
        {
            DiemSo diem = new DiemSo();
            diem.LopId = LopId;
            List<string> strCots = Cots.Select(n => n.ToString()).ToList();
            string strIn = string.Join(",", strCots.Select(n => "[" + n + "]"));
            string strSelect = string.Join(",", strCots.Select(n => "[" + n + "] Cot" + n));
            string sql = string.Format(@"
SELECT hv.MaHV, hv.HoLot + ' ' + hv.Ten HoTen, {0}
FROM HocVien hv
INNER JOIN (SELECT * FROM LopHocVien WHERE LopId = {{0}}) lhv 
	ON hv.MaHV = lhv.MaHV
LEFT JOIN
(
	SELECT MaHV, {1}
	FROM 
	(
		SELECT MaHV, [Cot], Diem FROM LopDiem
		WHERE LopId = {{0}}
	) p
	PIVOT
	(
		SUM(Diem)
		FOR [Cot] IN ({2})
	) AS pvt
) d ON hv.MaHV = d.MaHV
ORDER BY hv.MaHV
", strSelect, strIn, strIn);
            //strCots.AddRange(new []{"MaHV", "HoTen"});
            List<string> CotContract = new List<string>();
            CotContract.AddRange(Cots.Select(n => "Cot" + n));
            CotContract.AddRange(new[] { "MaHV", "HoTen" });
            diem.Diems = this.ObjectContext.ExecuteStoreQuery<HocVienDiem>(sql, LopId).ToList();
            return JsonConvert.SerializeObject(diem, Formatting.Indented,
                                               new JsonSerializerSettings 
                                               {
                                                   ContractResolver = new DiemSoContractResolver(CotContract.ToArray())
                                               });

            //return null;
        }

        public void SaveDiemSo(string JsonStr)
        {
            DiemSo ds = JsonConvert.DeserializeObject<DiemSo>(JsonStr);
            List<LopDiem> lstOriginal = this.ObjectContext.LopDiems.Where(n => ds.Cots.Contains(n.Cot)).ToList();
            List<LopDiem> lstClient = new List<LopDiem>();
            //this.ObjectContext.LopDiems.in
            LopDiem tmp;
            PropertyInfo pi;
            decimal? diem;
            foreach (int cot in ds.Cots)
            {
                foreach (HocVienDiem item in ds.Diems)
                {
                    pi = typeof (HocVienDiem).GetProperty("Cot" + cot);
                    diem = (decimal?)pi.GetValue(item, null);
                    if (diem != null && diem.HasValue)
                    {
                        tmp = new LopDiem();
                        tmp.Cot = cot;
                        tmp.MaHV = item.MaHV;
                        tmp.Diem = diem.Value;
                        lstClient.Add(tmp);
                    }
                }
            }
            Guid NguoiTao = (Guid) Membership.GetUser(HttpContext.Current.User.Identity.Name).ProviderUserKey;
            DateTime now = DateTime.Now;
            var lstAdd = lstClient.Except(lstOriginal, new LambdaComparer<LopDiem>((x, y) => x.MaHV == y.MaHV && x.Cot == y.Cot));
            var lstDelete = lstOriginal.Except(lstClient, new LambdaComparer<LopDiem>((x, y) => x.MaHV == y.MaHV && x.Cot == y.Cot));
            var lstUpdate = from LopDiem ldOriginal in lstOriginal
                            join LopDiem ldClient in lstClient
                                on new {ldOriginal.MaHV, ldOriginal.Cot} equals new {ldClient.MaHV, ldClient.Cot}
                            where ldOriginal.Diem != ldClient.Diem
                            select new {Original = ldOriginal, Client = ldClient};
            foreach (LopDiem ld in lstAdd)
            {
                ld.LopId = ds.LopId;
                ld.NgayTao = now;
                ld.NguoiTao = NguoiTao;
                this.ObjectContext.LopDiems.AddObject(ld);
            }
            foreach (LopDiem ld in lstDelete)
                this.ObjectContext.LopDiems.DeleteObject(ld);
            foreach (var item in lstUpdate)
                item.Original.Diem = item.Client.Diem;
            this.ObjectContext.SaveChanges();
        }

        #endregion
    }
}